/*/{Protheus.doc} SYNCIEX
E a User Functon de geracao do layout IEX (Documentos de Importacoo e Exportacao) do Sistema Synchro
@author Elton Teodoro Alves
@since 14/11/2014
@version P11 R5
@Param aParam,Array,Array enviado pelo schudele
@Example
Esta User Function foi desenvolvida para ser utilizada com o Schudele, que passa como parametro para User Function um array com a seguinte configuracao:</br>
</br>
<table>
<tr><th> POSICAO </th><th> TIPO </th><th> DESCRICAO             </th></tr>
<tr><td> [01]    </td><td> C    </td><td> Codigo da Empresa     </td></tr>
<tr><td> [02]    </td><td> C    </td><td> Codigo da Filial      </td></tr>
<tr><td> [03]    </td><td> C    </td><td> Codigo do Agendamento </td></tr>
<tr><td> [04]    </td><td> C    </td><td> Codigo da Tarefa      </td></tr>
</table>
</br>	
Para teste fora do schudele, pode-se enviar para o SmartClient com a clausula -a estes dados separadas por virgula ',' Ex.: 99,01,0001,0001
/*/

#Define CRLF Chr(13) + Chr(10)
Static PERIOD := 'D'

#Include 'Protheus.ch'

User Function SYNCIEX(xParam)
	
	Local	cLayout		:=	Right(ProcName(),3)
	Local	aParam		:=	If(Valtype(xParam) == 'C',Separa(xParam,',',.T.),xParam)
	Local	aSyncEnv	:=	U_SYNCENV(aParam,cLayout,PERIOD)
	Local	cTrab		:=	GetNextAlias()
	Local	cTempTrab	:=	cLayout + Dtos(Date()) + StrTran(Time(),':','')
	Local	cTempFile	:=	GetSrvProfString("StartPath","") + cTempTrab + GetDbExtension()
	Local	cQuery		:=	''
	Local	cQueryCD5	:=	''
	Local	cQueryCDL	:=	''
	Local	cQueryBkp	:=	''
	Local	aStru		:=	{}
	Local	nX			:=	0
	Local	aFiliais	:=	StrTokArr(AllTrim(aSyncEnv[10]),'/',)
	Local	cFiliais	:=	''
	Local	nCount		:=	0
	
	// Monta a string com a lista de filiais para ser utilizada na query
	
	For nX := 1 To Len(aFiliais)
		
		cFiliais += "'" + aFiliais[nX] + "'"
		
		If nX # Len(aFiliais)
			
			cFiliais += ","
			
		End If
		
	Next nX
	
	// --------------------------------------------------------------
	// Monta estrutura do arquivo temporario com dados a serem exportados
	// --------------------------------------------------------------
	aAdd(aStru,{'POS0001','C',0006,0}) // CHAVE_LOTE
	aAdd(aStru,{'POS0007','C',0010,0}) // ORIGEM
	aAdd(aStru,{'POS0017','C',0040,0}) // CHAVE_ORIGEM
	aAdd(aStru,{'POS0057','C',0001,0}) // CTRL_INSTRUCAO
	aAdd(aStru,{'POS0058','C',0040,0}) // DOF_IMPORT_NUMERO
	aAdd(aStru,{'POS0098','C',0020,0}) // INFORMANTE_EST_CODIGO
	aAdd(aStru,{'POS0118','C',0030,0}) // INFORMANTE_PFJ_ORIGEM
	aAdd(aStru,{'POS0148','C',0050,0}) // INFORMANTE_PFJ_CHAVE_ORIGEM
	aAdd(aStru,{'POS0198','C',0015,0}) // NUM_REG_EXPORT
	aAdd(aStru,{'POS0213','C',0015,0}) // NUM_DECL_IMPEXP
	aAdd(aStru,{'POS0228','C',0255,0}) // OBS
	aAdd(aStru,{'POS0483','C',0008,0}) // DT_EMI_RE_DI
	aAdd(aStru,{'POS0491','C',0008,0}) // DT_DECLARACAO
	aAdd(aStru,{'POS0499','C',0008,0}) // DT_RG_EXPORTACAO
	aAdd(aStru,{'POS0507','C',0008,0}) // DT_CONHECIMENTO
	aAdd(aStru,{'POS0515','C',0008,0}) // DT_CP_EXPORTACAO
	aAdd(aStru,{'POS0523','C',0001,0}) // DESPACHO_EXP
	aAdd(aStru,{'POS0524','C',0016,0}) // NUM_EMBARQUE
	aAdd(aStru,{'POS0540','C',0002,0}) // TIPO_TRANSP
	aAdd(aStru,{'POS0542','C',0008,0}) // NUM_COMPROVANTE
	aAdd(aStru,{'POS0550','C',0004,0}) // CODIGO_PAIS_MERC
	aAdd(aStru,{'POS0554','C',0003,0}) // SERIE_SUBSERIE
	aAdd(aStru,{'POS0557','C',0003,0}) // SERIE
	aAdd(aStru,{'POS0560','C',0008,0}) // SUB_SERIE
	aAdd(aStru,{'POS0568','C',0008,0}) // NUMERO
	aAdd(aStru,{'POS0576','C',0008,0}) // DATA_EMISSAO_NF
	aAdd(aStru,{'POS0584','C',0001,0}) // NAT_EXP
	aAdd(aStru,{'POS0585','C',0008,0}) // DT_AVB
	aAdd(aStru,{'POS0593','C',0001,0}) // IN_DOC_IMP_EXP
	
	// --------------------------------------------------------------
	
	If aSyncEnv[6]
		
		// --------------------------------------------------------------
		// Montagem da query de pesquisa
		// --------------------------------------------------------------
		cQueryCD5	+=	"SELECT " + CRLF
		cQueryCDL	+=	"SELECT " + CRLF
		
		cQueryCD5	+=	"'CD5' ORIGEM," + CRLF
		cQueryCDL	+=	"'CDL' ORIGEM," + CRLF
		
		cQueryCD5	+=	"SF1.R_E_C_N_O_ RECNO," + CRLF
		cQueryCDL	+=	"SF2.R_E_C_N_O_ RECNO," + CRLF
		
		//-----------------------------------------------
		
		// Mes Ano do Registro
		
		cQueryCD5	+=	"SF1.F1_DAUTNFE POS0001," + CRLF
		cQueryCDL	+=	"SF2.F2_DAUTNFE POS0001," + CRLF
		
		cQueryCD5	+=	"CD5.CD5_FILIAL POS0007," + CRLF
		cQueryCDL	+=	"CDL.CDL_FILIAL POS0007," + CRLF
		
		cQueryCD5	+=	"'' POS0017," + CRLF
		cQueryCDL	+=	"'' POS0017," + CRLF
		
		cQueryCD5	+=	"'I' POS0057," + CRLF
		cQueryCDL	+=	"'I' POS0057," + CRLF
		
		cQueryCD5	+=	"'' POS0058," + CRLF
		cQueryCDL	+=	"'' POS0058," + CRLF
		
		cQueryCD5	+=	"'' POS0098," + CRLF
		cQueryCDL	+=	"'' POS0098," + CRLF
		
		cQueryCD5	+=	"'' POS0118," + CRLF
		cQueryCDL	+=	"'' POS0118," + CRLF
		
		cQueryCD5	+=	"'' POS0148," + CRLF
		cQueryCDL	+=	"'' POS0148," + CRLF
		
		cQueryCD5	+=	"'' POS0198," + CRLF
		cQueryCDL	+=	"CDL.CDL_NRREG POS0198," + CRLF
		
		cQueryCD5	+=	"CD5.CD5_NDI POS0213," + CRLF
		cQueryCDL	+=	"CDL.CDL_NUMDE POS0213," + CRLF
		
		cQueryCD5	+=	"'' POS0228," + CRLF
		cQueryCDL	+=	"'' POS0228," + CRLF
		
		cQueryCD5	+=	"CD5.CD5_DTDI POS0483," + CRLF
		cQueryCDL	+=	"CDL.CDL_DTREG POS0483," + CRLF
		
		cQueryCD5	+=	"'' POS0491," + CRLF
		cQueryCDL	+=	"CDL.CDL_DTDE POS0491," + CRLF
		
		cQueryCD5	+=	"'' POS0499," + CRLF
		cQueryCDL	+=	"CDL.CDL_DTREG POS0499," + CRLF
		
		cQueryCD5	+=	"'' POS0507," + CRLF
		cQueryCDL	+=	"CDL.CDL_DTCHC POS0507," + CRLF
		
		cQueryCD5	+=	"'' POS0515," + CRLF
		cQueryCDL	+=	"CDL.CDL_DTAVB POS0515," + CRLF
		
		// Averbacao de Despacho
		cQueryCD5	+=	"'N' POS0523," + CRLF
		
		cQueryCDL	+=	"CASE CDL.CDL_DTAVB " + CRLF
		cQueryCDL	+=	"WHEN '" + Space(GetSx3Cache('CDL_DTAVB','X3_TAMANHO')) + "' THEN 'N'" + CRLF
		cQueryCDL	+=	"ELSE 'S'" + CRLF
		cQueryCDL	+=	"END POS0523," + CRLF
		// ----
		
		cQueryCD5	+=	"'' POS0524," + CRLF
		cQueryCDL	+=	"CDL.CDL_CHCEMB POS0524," + CRLF
		
		cQueryCD5	+=	"'' POS0540," + CRLF
		cQueryCDL	+=	"CDL.CDL_TPCHC POS0540," + CRLF
		
		cQueryCD5	+=	"'' POS0542," + CRLF
		cQueryCDL	+=	"'' POS0542," + CRLF
		
		cQueryCD5	+=	"'' POS0550," + CRLF
		cQueryCDL	+=	"CDL.CDL_PAIS POS0550," + CRLF
		
		cQueryCD5	+=	"SF1.F1_SERIE POS0554," + CRLF
		cQueryCDL	+=	"SF2.F2_SERIE POS0554," + CRLF
		
		cQueryCD5	+=	"'' POS0557," + CRLF
		cQueryCDL	+=	"'' POS0557," + CRLF
		
		cQueryCD5	+=	"'' POS0560," + CRLF
		cQueryCDL	+=	"'' POS0560," + CRLF
		
		cQueryCD5	+=	"SF1.F1_DOC POS0568," + CRLF
		cQueryCDL	+=	"SF2.F2_DOC POS0568," + CRLF
		
		cQueryCD5	+=	"SF1.F1_DAUTNFE POS0576," + CRLF
		cQueryCDL	+=	"SF2.F2_DAUTNFE POS0576," + CRLF
		
		cQueryCD5	+=	"'0' POS0584," + CRLF
		cQueryCDL	+=	"'0' POS0584," + CRLF
		
		cQueryCD5	+=	"'" + Replicate('0',8) + "' POS0585," + CRLF
		cQueryCDL	+=	"'" + Replicate('0',8) + "' POS0585," + CRLF
		
		// Indica DE ou DI Simplificada
		cQueryCD5	+=	"CASE CD5.CD5_TPIMP " + CRLF
		cQueryCD5	+=	"WHEN '0' THEN 'N' " + CRLF
		cQueryCD5	+=	"ELSE 'S' " + CRLF
		cQueryCD5	+=	"END POS0593" + CRLF
		
		cQueryCDL	+=	"CASE CDL.CDL_INDDOC " + CRLF
		cQueryCDL	+=	"WHEN '0' THEN 'N' " + CRLF
		cQueryCDL	+=	"ELSE 'S' " + CRLF
		cQueryCDL	+=	"END POS0593" + CRLF
		
		// --------- FROM CD5 CDL
		
		cQueryCD5 +=	"FROM " + RetSqlName("CD5") + " CD5 " + CRLF
		cQueryCDL +=	"FROM " + RetSqlName("CDL") + " CDL " + CRLF
		
		// --------- LEFT JOIN CD5
		
		cQueryCD5	+=	"LEFT JOIN " + RetSqlName("SF3") + " SF3 ON " + CRLF
		cQueryCD5	+=	"CD5.CD5_FILIAL = SF3.F3_FILIAL AND " + CRLF
		cQueryCD5	+=	"CD5.CD5_SERIE = SF3.F3_SERIE AND " + CRLF
		cQueryCD5	+=	"CD5.CD5_DOC = SF3.F3_NFISCAL AND " + CRLF
		cQueryCD5	+=	"CD5.CD5_FORNEC = SF3.F3_CLIEFOR AND " + CRLF
		cQueryCD5	+=	"CD5.CD5_LOJA = SF3.F3_LOJA " + CRLF
		
		cQueryCD5	+=	"LEFT JOIN " + RetSqlName("SF1") + " SF1 ON " + CRLF
		cQueryCD5	+=	"SF3.F3_FILIAL = SF1.F1_FILIAL AND " + CRLF
		cQueryCD5	+=	"SF3.F3_SERIE = SF1.F1_SERIE AND " + CRLF
		cQueryCD5	+=	"SF3.F3_NFISCAL = SF1.F1_DOC AND " + CRLF
		cQueryCD5	+=	"SF3.F3_CLIEFOR = SF1.F1_FORNECE AND " + CRLF
		cQueryCD5	+=	"SF3.F3_LOJA = SF1.F1_LOJA " + CRLF
		
		// --------- WHERE CD5
		
		cQueryCD5	+=	"WHERE SF1.F1_DAUTNFE = '" + aSyncEnv[9] + "' AND " + CRLF
		cQueryCD5	+=	"ISNULL(CD5.D_E_L_E_T_,'') <> '*' AND " + CRLF
		cQueryCD5	+=	"ISNULL(SF3.D_E_L_E_T_,'') <> '*' AND " + CRLF
		cQueryCD5	+=	"ISNULL(SF3.F3_CFO,'') LIKE '3%' AND " + CRLF
		cQueryCD5	+=	"ISNULL(SF1.D_E_L_E_T_,'') <> '*' " + CRLF
		
		// --------- LEFT JOIN CDL
		
		cQueryCDL	+=	"LEFT JOIN " + RetSqlName("SF3") + " SF3 ON " + CRLF
		cQueryCDL	+=	"CDL.CDL_FILIAL = SF3.F3_FILIAL AND " + CRLF
		cQueryCDL	+=	"CDL.CDL_SERIE = SF3.F3_SERIE AND " + CRLF
		cQueryCDL	+=	"CDL.CDL_DOC = SF3.F3_NFISCAL AND " + CRLF
		cQueryCDL	+=	"CDL.CDL_CLIENT = SF3.F3_CLIEFOR AND " + CRLF
		cQueryCDL	+=	"CDL.CDL_LOJA = SF3.F3_LOJA " + CRLF
		
		cQueryCDL	+=	"LEFT JOIN " + RetSqlName("SF2") + " SF2 ON " + CRLF
		cQueryCDL	+=	"SF3.F3_FILIAL = SF2.F2_FILIAL AND " + CRLF
		cQueryCDL	+=	"SF3.F3_SERIE = SF2.F2_SERIE AND " + CRLF
		cQueryCDL	+=	"SF3.F3_NFISCAL = SF2.F2_DOC AND " + CRLF
		cQueryCDL	+=	"SF3.F3_CLIEFOR = SF2.F2_CLIENTE AND " + CRLF
		cQueryCDL	+=	"SF3.F3_LOJA = SF2.F2_LOJA " + CRLF
		
		// --------- WHERE CDL
		
		cQueryCDL	+=	"WHERE SF2.F2_DAUTNFE = '" + aSyncEnv[9] + "' AND " + CRLF
		cQueryCDL	+=	"ISNULL(CDL.D_E_L_E_T_,'') <> '*' AND " + CRLF
		cQueryCDL	+=	"ISNULL(SF3.D_E_L_E_T_,'') <> '*' AND " + CRLF
		cQueryCDL	+=	"ISNULL(SF3.F3_CFO,'') LIKE '7%' AND " + CRLF
		cQueryCDL	+=	"ISNULL(SF2.D_E_L_E_T_,'') <> '*' " + CRLF
		
		// --------- UNION ALL
		
		cQuery	+=	cQueryCD5
		cQuery	+=	"UNION ALL " + CRLF
		cQuery	+=	cQueryCDL
		
		cQueryBkp	:=	cQuery
		
		cQuery := ChangeQuery(cQuery)
		
		If TcSqlExec(cQuery) < 0
			
			U_SYNCMAIL(AllTrim(aSyncEnv[15]),cLayout + ' - ' + AllTrim(aSyncEnv[17]),'Erro na execucao da query no banco de dados: ' + Chr(13) + Chr(10) + AllTrim(TcSqlError()),AllTrim(SubStr(aSyncEnv[4],1,6)),AllTrim(SubStr(aSyncEnv[4],7,6)),1,aSyncEnv[8],aSyncEnv[9])
			
		Else
			
			DbUseArea(.T.,"TOPCONN",TCGenQry(,,cQuery),cTrab,.F.,.T.)
			
			U_SYNCLOG(;
				'Agenda : ' +  SubStr(aSyncEnv[4],1,6) + CRLF +;
				'Tarefa : ' +  SubStr(aSyncEnv[4],7,6) + CRLF +;
				cLayout + ' - ' + AllTrim(aSyncEnv[17]) + CRLF +;
				'Query executada com sucesso: ' + CRLF + cQueryBkp)
			
			// Cria arquivo temporario
			
			fErase(Separa(cTempFile,'\',.T.)[3])
			DbCreate(cTempFile,aStru,__LocalDriver)
			DbUseArea(.T.,__LocalDriver,cTempFile,cTempTrab,.T.,.F.)
			
			U_SYNCLOG(;
				'Agenda : ' +  SubStr(aSyncEnv[4],1,6) + CRLF +;
				'Tarefa : ' +  SubStr(aSyncEnv[4],7,6) + CRLF +;
				'Arquivo Temporario criado com sucesso : ' + cTempFile)
			
			// --------------------------------------------------------------
			// Preenchimento do arquivo temporario
			// --------------------------------------------------------------
			
			(cTrab)->(DbGotop())
			
			Do While .Not. (cTrab)->(EOF())
				
				RecLock(cTempTrab,.T.)
				
				(cTempTrab)->POS0001 := (cTrab)->POS0001
				(cTempTrab)->POS0007 := U_SYNCFIL(aSyncEnv[7],5,(cTrab)->POS0007)
				(cTempTrab)->POS0017 := (cTrab)->POS0568 + (cTrab)->POS0554 + (cTrab)->POS0001
				(cTempTrab)->POS0057 := (cTrab)->POS0057
				(cTempTrab)->POS0058 := U_SYNCFIL(aSyncEnv[7],5,(cTrab)->POS0007) + Str((cTrab)->RECNO,10) + (cTrab)->POS0568 + (cTrab)->POS0554 + (cTrab)->POS0001
				(cTempTrab)->POS0098 := U_SYNCFIL(aSyncEnv[7],2,(cTrab)->POS0007) + U_SYNCFIL(aSyncEnv[7],3,(cTrab)->POS0007)
				(cTempTrab)->POS0118 := (cTrab)->POS0118
				(cTempTrab)->POS0148 := (cTrab)->POS0148
				
				// Numero do Registro de Exportacao
				If Empty((cTrab)->POS0198)
					
					(cTempTrab)->POS0198 := Replicate('0',15)
					
				Else
					
					(cTempTrab)->POS0198 := (cTrab)->POS0198
					
				End If
				
				(cTempTrab)->POS0213 := (cTrab)->POS0213
				(cTempTrab)->POS0228 := (cTrab)->POS0228
				(cTempTrab)->POS0483 := (cTrab)->POS0483
				(cTempTrab)->POS0491 := (cTrab)->POS0491
				(cTempTrab)->POS0499 := (cTrab)->POS0499
				(cTempTrab)->POS0507 := (cTrab)->POS0507
				(cTempTrab)->POS0515 := (cTrab)->POS0515
				(cTempTrab)->POS0523 := (cTrab)->POS0523
				(cTempTrab)->POS0524 := (cTrab)->POS0524
				(cTempTrab)->POS0540 := (cTrab)->POS0540
				(cTempTrab)->POS0542 := (cTrab)->POS0542
				(cTempTrab)->POS0550 := (cTrab)->POS0550
				(cTempTrab)->POS0554 := (cTrab)->POS0554
				(cTempTrab)->POS0557 := (cTrab)->POS0557
				(cTempTrab)->POS0560 := (cTrab)->POS0560
				(cTempTrab)->POS0568 := (cTrab)->POS0568
				(cTempTrab)->POS0576 := (cTrab)->POS0576
				(cTempTrab)->POS0584 := (cTrab)->POS0584
				(cTempTrab)->POS0585 := (cTrab)->POS0585
				(cTempTrab)->POS0593 := (cTrab)->POS0593
				
				MsUnLock()
				
				(cTrab)->(DbSkip())
				
				nCount++
				
			End Do
			
			// --------------------------------------------------------------
			
			U_SYNCDAT(cTempTrab,aSyncEnv,nCount)
			
			(cTrab)->(DbCloseArea())
			(cTempTrab)->(DbCloseArea())
			fErase(Separa(cTempFile,'\',.T.)[3])
			
		End IF
		
	Else
		
		U_SYNCLOG(;
			'Agenda : ' +  SubStr(aSyncEnv[4],1,6) + CRLF +;
			'Tarefa : ' +  SubStr(aSyncEnv[4],7,6) + CRLF +;
			'Nao foi possivel montar o ambiente de geracao do layout ' + cLayout)
		
	End IF
	
	RpcClearEnv()
	
Return
